package com.xkw.autocode.jdbc.mysql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.xkw.autocode.jdbc.entity.ColumnEntity;
import com.xkw.autocode.jdbc.entity.TableEntity;

public class JdbcExecutor {

	private static final Logger logger = LoggerFactory.getLogger(JdbcExecutor.class);

	private static final String SQL_TABLES = "SELECT DISTINCT TABLE_NAME,TABLE_COMMENT FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=(select DATABASE()) ";
	private static final String SQL_COLUMN = "SELECT DISTINCT COLUMN_NAME,DATA_TYPE,COLUMN_TYPE,COLUMN_KEY,COLUMN_COMMENT,COLUMN_DEFAULT,CHARACTER_MAXIMUM_LENGTH as LENGTH FROM  INFORMATION_SCHEMA.COLUMNS"
			+ " WHERE TABLE_NAME='{tableName}' and TAble_schema=(select DATABASE())";

	private DBHelper dBHelper;

	public JdbcExecutor(String url, String username, String password) {

		// 创建DBHelper对象
		dBHelper = new DBHelper(url, username, password);
	}

	/**
	 * 获取表名和注释
	 * 
	 * @return
	 */
	public List<TableEntity> getTables() {

		List<TableEntity> resultList = new ArrayList<>();

		ResultSet ret = null;
		PreparedStatement pst = null;
		try {
			pst = dBHelper.getStatement(SQL_TABLES);
			
			// 执行语句，得到结果集
			ret = pst.executeQuery();
			while (ret.next()) {
				
				TableEntity tableEntity = new TableEntity();
				tableEntity.setTableName(ret.getString(ColumnNameConstants.TABLE_NAME));
				tableEntity.setComments(ret.getString(ColumnNameConstants.TABLE_COMMENT));
				
				resultList.add(tableEntity);
			}
		} catch (SQLException e) {
			logger.error("", e);
		} finally {
			// 关闭连接
			try {
				if (ret != null) {
					ret.close();
				}
				if (pst != null) {
					pst.close();
				}
			} catch (SQLException e) {
				logger.error("", e);
			}
		}
		return resultList;
	}

	public List<ColumnEntity> getTableColumnInfo(String tableName) {
		String sql = SQL_COLUMN.replace("{tableName}", tableName);

		List<ColumnEntity> resultList = new ArrayList<>();

		ResultSet ret = null;
		PreparedStatement pst = null;
		try {
			pst = dBHelper.getStatement(sql);
			ret = pst.executeQuery();// 执行语句，得到结果集
			while (ret.next()) {
				ColumnEntity columnEntity = new ColumnEntity();
				
				columnEntity.setColumnName(ret.getString(ColumnNameConstants.COLUMN_NAME));
				columnEntity.setComments(ret.getString(ColumnNameConstants.COLUMN_COMMENT));
				columnEntity.setJdbcType(ret.getString(ColumnNameConstants.COLUMN_TYPE));
				columnEntity.setDefaultValue(ret.getString(ColumnNameConstants.COLUMN_DEFAULT));
				resultList.add(columnEntity);
			}
		} catch (SQLException e) {
			logger.error("", e);
		} finally {
			// 关闭连接
			try {
				if (ret != null) {
					ret.close();
				}
				if (pst != null) {
					pst.close();
				}
			} catch (SQLException e) {
				logger.error("", e);
			}
		}
		return resultList;
	}

	public void close() {
		this.dBHelper.close();
	}
}
